#!/usr/bin/perl -w
use strict;
use Getopt::Long;

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Usage

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

my $usage = <<'USAGE';

USAGE:

    panda_create_pi_fasta_from_pi_list.pl

                                  --results /local/results/panda.fasta
                                  --temp_dir /local/databases/panda
                                  --db_login "dbname db_user"
                                  --pi_list /local/databases/panda/pi.list
                                  [--where "WHERE sequences.len=45"]
                                  [--help]

        'dbname' is the database name and 'db_user' is a valid user
        --temp_dir should be read/write accessible to postgreSQL
        --where is an optional parameter to filter the sequences
                using a SQL 'WHERE' clause.
                In this case, sequences are included only
                if they have lengths of 45.
        --pi_list contains a list of panda numbers in the
                following format:
                num1<tab>num2<newline>
                num1<tab>num2<newline>
RESULTS:

  --results     All matching Panda sequences from a set of supplied panda IDs
                These must be match the criteria specified by "--where"
                The accession line will contain the panda ID.
                This file must be write accessible from the PostgreSQL DB.


    This script extracts all sequences from panda matching a
    supplied list of panda numbers.


USAGE


#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Command line options

#88888888888888888888888888888888888888888888888888888888888888888888888888888888
my $TEMP_DIR;
my $results = '';
my $DB_LOGIN;
my $pi_list = '';
my $where = '';
my $help;
GetOptions(	'temp_dir=s'	=> \$TEMP_DIR,
			'results=s'		=> \$results,
			'db_login=s'	=> \$DB_LOGIN,
			'pi_list=s'		=> \$pi_list,
			'where=s'		=> \$where,
			'help'			=> \$help);

			
die $usage if ($help);
die "\nERROR:\n\tMissing Argument: db_login\n" .$usage
				unless ($DB_LOGIN);
die "\nERROR:\n\tMissing Argument: temp_dir\n" .$usage
				unless ($TEMP_DIR);
die "\nERROR:\n\tMissing Argument: pi_list\n" .$usage
				unless ($pi_list);
die "\nERROR:\n\tMissing Argument: results\n" .$usage
				unless ($results);




#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Create SQL file

#88888888888888888888888888888888888888888888888888888888888888888888888888888888
# pre-pend SQL WHERE to $where string
$where = 'WHERE '.$where unless ($where eq '');

# remove trailing slash
$TEMP_DIR =~ s/\/$//;


open (TMP_FILE, ">$TEMP_DIR/tmp.sql") or die "Couldn't open $TEMP_DIR/tmp.sql\n";
while (<DATA>)
{
	s/TEMP_DIR/$TEMP_DIR/;
	s/PI_LIST/$pi_list/;
	s/WHERE/$where/;
	s/RESULTS/$results/;
	print TMP_FILE;
}


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Execute SQL file and cleanup

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

# call psql
print STDERR "\n", ("*" x 70) ,"\n";
system ("psql ".$DB_LOGIN. " -t -F \"\t\" -A -q -f $TEMP_DIR/tmp.sql");

# merge output to FASTA format
system ("panda_output_to_fasta ".
		"TEMP_DIR/pi.fa.tmp ".
		"TEMP_DIR/pi.fa");
my ($s, $m, $h) = localtime(time);
printf STDERR "Convert sequences into FASTA format. Finish at %02d:%02d:%02d\n", $h,$m,$s;
print STDERR "\n", ("*" x 70) ,"\n";

# cleanup
system ("rm -f TEMP_DIR/pi.fa.tmp");
system ("rm $TEMP_DIR/tmp.sql");





#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	SQL instructions for PSQL follows

#88888888888888888888888888888888888888888888888888888888888888888888888888888888
__DATA__

SELECT 'BEGIN  Loading list of panda numbers at ' || current_time;
CREATE TEMP TABLE t_pi (pi1 OID, pi2 OID);
COPY  t_pi from 'PI_LIST';
CREATE UNIQUE INDEX i_pi on t_pi (pi1, pi2);
SELECT 'FINISH Loading ' || count(*) || ' at ' || current_time from t_pi;


SELECT 'BEGIN  List all matching sequences from Panda at ', current_time;
SELECT 'Save to TEMP_DIR/pi.fa.tmp';
\o 'RESULTS'
SELECT	
		pi1,
		pi2,
		sequence
			FROM sequences
			NATURAL JOIN t_pi;

\o
SELECT 'FINISH at ', current_time;
drop INDEX i_pi;
